ADVANCED DATABASES REPORT
Theater Organization
17 JANVIER 2021
EFREI PARIS
VARATHALINGAM Mayouran – DELPHIN Gilbert
1
Table of Contents
I. Introduction / M&G………………………….2
II. Short description of the Network….……..2
III. Modelization/ Function / Queries /
Triggers / Procedures………………………3
I. ERD & Relational Schema……………….3
II. Functions/ Queries /Triggers /
Procedures
1. Organization………………………..5
2. Ticketing…………………………….7
3. Accounting………………………...12
4. Network…………………………….17
IV. Encountered Problems…………………...19
V. Conclusion …………………………………20
2
I. Introduction / M&G
Theater is the art of writing and performing plays but it’s also the structure which have
rows seats, from where people can watch a performance. In fact, theaters are less and less
watched by people due to all other activities deemed more interesting.
So how can all theatrical companies still survive in these years of digital dominance?
To face this problem some theatrical companies had the idea of making a common
management of all their shows, so their theaters can welcome many other theatrical
companies and their staff can play in other places and not only on their theater respectively.
M&G our team is composed of VARATHLINGAM Mayouran and DELPHIN Gilbert, we
are main partners for these theaters to create all the needed databases. Our job is to help
them to manage correctly this network and optimize it to see all the advantages.
II. Short description of this network
How can these theatrical companies manage this new network?
If a theater want a company to play their show on their place, the theater has to pay
the company some costs ( comedians’ fees , staging costs and travel costs).
It’s not the case if the theater want it’s company to perform (there will be no travel costs).
Concerning the ticketing all the revenues from any representation go to the theater
where the show took place. For every representation there is an Initial Price and some
promotions can be applied depending on the situation of the client (Student , Children..) , days
separating the representation date and the ticket sell date or the load factor of the theater
for the particular representation.
Moreover, some grants are accorded to theaters depending of their cities or
supporter. For the case of Municipalities or Ministry of Culture there are negotiated with the
theater and have fixed durations.
So, we should consider all these functionalities and add some to correctly manages
this network.
3
III. Modelization/ Functions/ Queries / Triggers / Procedures
I. ERD & Relational Schema
4
5
II. Functions / Queries / Triggers / Procedures
We model our network to answers some questions.
1.Organization
Is no representation of a company does not overlap anothor one?
For answering this question, we created a trigger to not allow another show to happened on
the same theater at the same date.
So, this trigger count if there is any representation which has the same date that the one which
we want to enter, if it’s not the case it’ll let it insert but if it’s we will have an error message.
So, let’s try an example to see if this trigger works.
We put the second representation at the same date and theater like the first one.
We tried to run it, but this message appeared.
It shows that the trigger works, and we answered that question correctly.
6
What is the set of cities in which a company plays for a certain time period?
So, we had to do 2 Inner Join to access all the columns we want, and we have as
a result the cities where all companies play.
7
2.Ticketing
So first we will create a trigger to fill the TicketSold table, this table contains the TicketId and
the FinalPrice with the promotion added.
So, depending on the Status of the client TicketSold will contain the price the client paid.
So, there are many Status. Children, Student etc but we also have C1, C2,
C3 that are respectively those promotion.
You can see that Ticket have all information concerning the initial price the client and the
ticket buying date but Ticket Sold has the exact amount of what the client paid to the cash
desk.
8
9
We can now answer to three questions concerning Ticketing.
What is the ticket price today?
For this example, we took the date 27/12/2020 as today date.
This trigger check the date condition,
in fact if it’s 15 days before the representation the clinet can benefit 20% of reduction on
the initial price and it’s also the case if it’s the day of representation and the
room is not filled enough ( less than 50% and less than 30%)
The help of function was needed to correctly make the trigger work. Those function returned the
Theater capacity and the number of Ticket Sold to check promotion conditions on the trigger.
10
So, we can see that if a client wants to know the initial prices for shows in the theater, it’ll shows
us those shows with prices different from the initial price and depending on the date and the filling of
shows.
What is the distribution of tickets by price?
The second question is concerning the distribution of ticket price, in fact with discounts
applied on tickets the same tickets shows can be sold with different prices.
11
What is the average load factor?
So first what is the definition of average load?
We took the number of people who bought tickets for a specific show divide by the capacity
of the theater in which the show will be played.
The help of functions was needed.
We created a procedure to shows us the average load factor of each theater.
Those numbers are very low considering our database with few tickets sold but a great
capacity for theaters.
12
3.Accounting
For this part we created a table name TheaterBalance, a table that will allow each theater to
know their current balance. To make this table work perfectly, we created a trigger that will
update the current balance of a theater for each ticket sold.
Basically, this trigger will be activated if we insert a new ticket in the table Ticket. So, if a new
ticket is sold today (it means that we insert a new ticket) then this trigger will update the
current balance of the theater concerned. And of course, this trigger checks the client type to
apply an eventual discount.
Here is an example:
We inserted a new ticket today, for the representation 7. This representation takes place in
the 8th theater, so it means that in the table TheaterBalance the trigger will update the 8th
theater’s current balance which was in the beginning at 0.
13
Here we can see that the trigger updated the 8th theater’s current balance. It was at zero but
now it is at 32. It is the price of the ticket sold. But we have a problem here. In fact, when a
show is programmed for a theater, it induces that the theater has to pay a global price as
indicated in the subject. To do this we created another trigger that will update the table
TheaterBalance each time we insert a new cost. Actually, a new cost will be inserted when a
new representation is programmed.
Here is an example:
Here we inserted two new representation that are programmed for today. We can see that
one take place in the 7th theater and the other one in the 8th.
As we inserted two new representations, we will also insert their costs.
Normally, the trigger has updated the TheaterBalance table.
14
We can see that the current balance of the concerned theater has been updated. Now we
have a fully functional TheaterBalance table.
To be honest we did not really understand the following questions.
So, what we did is that if the current balance of a theater is less or equal to 0 then the balance
will be red. Else the balance will be green. You can refer to our trigger’s screenshots above to
see when we update the balance status.
A show given by a company was sold out with a certain price. Is it cost effective for the
company? (Compared to costs incurred (salaries, travel, staging))
To answer this question, we created a procedure that displays each companies’ final cost for
each show.
Before
After
15
Here we can see the final cost of each companies for each show. We can clearly see if it was
cost effective for the company or not.
Was it the effective cost for the theater? (Costs / ticketing)
For this question, we did the same as before. We created a procedure that displays the final
cost of each theater for each show.
16
We can see that some theaters don’t have any cost, it is because no tickets were sold yet for
this show.
17
4.Network
The last section is concerning the network itself.
Are there companies that will never play in their theater?
So, for answering this question we checked if some companies ever played on their theater
with the help of this function that return the number of companies that play in their theater.
Then with the procedure depending on companies we display companies that never played in
their theater
The small problem with this statement is that it can display the same company if it plays many
times outside and never in their theater.
18
Now let’s see the most popular shows in a certain period
First, the most popular show in number of representations played in the month of December.
Then the most popular show regarding the number of seats sold.
19
IV. Encountered Problems
The first problem we encountered was about the software itself. In fact, we were used to
use sql server management studio. So, we naturally began our project with this software.
But we quickly realized that the syntax for triggers was not the same as the one we saw in
class with oracle. So, we wasted a big amount of time searching how to continue the project.
We tried on the oracle VM, but we could not import our initial script from our computer.
Furthermore, oracle VM was not the ideal software to use for a project.
We finally use Oracle Live Sql to do our project even if it was not the ideal software for a
project too. Our session expired multiple times while we were coding and we had to re-run
all the statements we did previously. Moreover, we also had many errors while coding on
live sql, errors that we didn’t understand the first time. The syntax in Oracle change from
what we used to do in sql server, so errors due to syntax and others occurred frequently.
And here again we spent a lot of time to understand and correct the errors even if it was a
small one. Finally, we managed to finish the project but honestly, we think that the next
time, it will be good to have a proper software in which we practiced. So that we can do our
project according to what we saw in class without wasting time.
20
V. Conclusion
In conclusion, we must admit that we are proud of our project and our investment of
time to finish correctly all queries.
This project helped us to understand how to use triggers, functions and procedures
but also to improve our skills in SQL. Despite having some difficulties with the program used,
SQL Oracle was a new program for us but thanks to our TP on triggers and function we
understand easily how to use Oracle SQL Developer. Again, we would like to thank Mr. Issam
Falih for his help concerning the understanding of the project. For further details about the
code please check our commented script on triggers/functions/procedures.